package org.jks.excel;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jks.model.FlightAngle;
import org.jks.model.FlightLine;
import org.jks.model.TowerCoordinate;
import org.jks.response.model.ActionResult;
import org.jks.service.FlightAngleService;
import org.jks.service.FlightLineService;
import org.jks.service.TowerCoordinateService;
import org.jks.util.ExcelUtil;
import org.springframework.web.multipart.MultipartFile;

public class FlightLineUtil {
	public static List<String> TowerCoordinateColumns = new ArrayList<String>();
	{
		TowerCoordinateColumns.add("杆塔编号");
		TowerCoordinateColumns.add("经度(度)");
		TowerCoordinateColumns.add("经度(分)");
		TowerCoordinateColumns.add("经度(秒)");
		TowerCoordinateColumns.add("纬度(度)");
		TowerCoordinateColumns.add("纬度(分)");
		TowerCoordinateColumns.add("纬度(秒)");
	}
	
	public static List<String> FlightAngleColumns = new ArrayList<String>();
	{
		FlightAngleColumns.add("杆塔编号");
		FlightAngleColumns.add("冰");
		FlightAngleColumns.add("转角角度");
		FlightAngleColumns.add("接地型式");
		FlightAngleColumns.add("电阻");
	}
	
	public static boolean checkTowerCoordinateColumnValid(String titles[]){
		int count = 0;
		for(String title: titles){
			if(TowerCoordinateColumns.contains(title)){
				count++;
			}
		}
		return count == TowerCoordinateColumns.size();
	}
	
	public static boolean checkFlightAngleColumnValid(String titles[]){
		int count = 0;
		for(String title: titles){
			if(FlightAngleColumns.contains(title)){
				count++;
			}
		}
		return count == FlightAngleColumns.size();
	}
	
	@SuppressWarnings("resource")
	public static void importExcel(FlightLineService flightLineService, 
			TowerCoordinateService towerCoordinateService,
			FlightAngleService flightAngleService,
			MultipartFile file, ActionResult ar){
		InputStream is;
		try {
			is = file.getInputStream();
		} catch (IOException e) {
			ar.setMsg("上传文件失败");
			e.printStackTrace();
			return;
		}
		String filename = file.getOriginalFilename();
		String name = ExcelUtil.getFilename(filename);
		FlightLine flightLine = flightLineService.findByName(name);
		if(flightLine != null){
			ar.setMsg("该线路已经存在");
			return;
		}
		flightLine = new FlightLine();
		flightLine.setName(name);
		
		XSSFWorkbook wb = null;
		XSSFSheet sheet_tower = null;
		XSSFSheet sheet_angle = null;
		XSSFRow row = null;
		int totalRows = 0;
		int colNum = 0;
		try {
			OPCPackage opcPackage = OPCPackage.open(is);
			wb = new XSSFWorkbook(opcPackage);
		}catch (Exception e) {
			ar.setMsg("创建文件失败");
			e.printStackTrace();
			return;
		}
		sheet_tower = wb.getSheetAt(0);
		row = sheet_tower.getRow(0);
		totalRows = sheet_tower.getPhysicalNumberOfRows();
		colNum = row.getPhysicalNumberOfCells();
		String[] title_towers = new String[colNum];
		for (int i = 0; i < colNum; i++) {
			title_towers[i] = ExcelUtil.getCellFormatValue(row.getCell((short) i));
		}
		if(!checkTowerCoordinateColumnValid(title_towers)){
			ar.setSuccess(true);
			ar.setMsg("无效的excel");
			return;
		}
		sheet_angle = wb.getSheetAt(1);
		row = sheet_angle.getRow(0);
		totalRows = sheet_angle.getPhysicalNumberOfRows();
		colNum = row.getPhysicalNumberOfCells();
		String[] title_angles = new String[colNum];
		for (int i = 0; i < colNum; i++) {
			title_angles[i] = ExcelUtil.getCellFormatValue(row.getCell((short) i));
		}
		if(!checkFlightAngleColumnValid(title_angles)){
			ar.setSuccess(true);
			ar.setMsg("无效的excel");
			return;
		}
		
		row = sheet_tower.getRow(0);
		totalRows = sheet_tower.getPhysicalNumberOfRows();
		colNum = row.getPhysicalNumberOfCells();
		List<TowerCoordinate> towerCoordinates = new ArrayList<TowerCoordinate>();
		for(int rownum=1; rownum<totalRows; rownum++){
			TowerCoordinate towerCoordinate = new TowerCoordinate();
			row = sheet_tower.getRow(rownum);
			towerCoordinate.setNo(ExcelUtil.getCellFormatValue(row.getCell(0)));
			//经度(度)
			float degree = Float.parseFloat(ExcelUtil.getCellFormatValue(row.getCell(1)));
			towerCoordinate.setLongitudeDegrees(degree);
			//经度(分)
			towerCoordinate.setLongitudeMinutes(Float.parseFloat(ExcelUtil.getCellFormatValue(row.getCell(2))));
			//经度(秒)
			towerCoordinate.setLongitudeSeconds(Float.parseFloat(ExcelUtil.getCellFormatValue(row.getCell(3))));
			//towerCoordinate.setLongitudeDegrees(degree + towerCoordinate.getLongitudeMinutes()/60 + towerCoordinate.getLongitudeSeconds()/3600);
			
			//纬度(度)
			degree = Float.parseFloat(ExcelUtil.getCellFormatValue(row.getCell(4)));
			towerCoordinate.setLatitudeDegrees(degree);
			//纬度(分)
			towerCoordinate.setLatitudeMinutes(Float.parseFloat(ExcelUtil.getCellFormatValue(row.getCell(5))));
			//纬度(秒)
			towerCoordinate.setLatitudeSeconds(Float.parseFloat(ExcelUtil.getCellFormatValue(row.getCell(6))));
			//towerCoordinate.setLatitudeDegrees(degree + towerCoordinate.getLatitudeMinutes()/60 + towerCoordinate.getLatitudeSeconds()/3600);
			
			towerCoordinate.setFlightLine(flightLine);
			towerCoordinates.add(towerCoordinate);
		}
		flightLine.setTowerCoordinates(towerCoordinates);
		
		row = sheet_angle.getRow(0);
		totalRows = sheet_angle.getPhysicalNumberOfRows();
		colNum = row.getPhysicalNumberOfCells();
		List<FlightAngle> flightAngles = new ArrayList<FlightAngle>();
		for(int rownum=1; rownum<totalRows; rownum++){
			FlightAngle flightAngle = new FlightAngle();
			row = sheet_angle.getRow(rownum);
			flightAngle.setNo(ExcelUtil.getCellFormatValue(row.getCell(0)));
			flightAngle.setIce(ExcelUtil.getCellFormatValue(row.getCell(1)));
			flightAngle.setCornerAngle(ExcelUtil.getCellFormatValue(row.getCell(2)));
			flightAngle.setGroundingType(ExcelUtil.getCellFormatValue(row.getCell(3)));
			flightAngle.setResistance(Float.parseFloat(ExcelUtil.getCellFormatValue(row.getCell(4))));
			flightAngle.setFlightLine(flightLine);
			flightAngles.add(flightAngle);
		}
		flightLine.setFlightAngles(flightAngles);
		try{
			flightLineService.save(flightLine);
			ar.setSuccess(true);
		}
		catch(Exception e){
			ar.setMsg("文件数据存在错误");
		}
	}
}
